package com.xm.dao.reception_desk;

import com.xm.entity.*;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 购物车Dao
 *
 * @author 康文程
 * @date 2024-1-3 下午 02:58
 */
public class ShoppingCartDao extends BaseUtil {
    /**
     * 添加商品到购物车
     *
     * @param userId           用户ID
     * @param commodityId      商品ID
     * @param specificationIds 规格Id 多个Id用,分隔
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean addToShoppingCart(Integer userId, Integer commodityId, String specificationIds, String insuranceIds) throws SqlErrorException {
        @Language("sql")
        String sql = "INSERT INTO shopping_cart (is_selected_status, quantity, information_id, " +
                "commodity_id,specification_ids,insurance_ids) VALUES (?,?,?,?,?,?)";
        Object[] params = {0, 1, userId, commodityId, specificationIds, insuranceIds};
        int result = executeUpdate(sql, params);
        return result == 1;
    }

    /**
     * 从购物车中删除商品
     *
     * @param shoppingCartId 购物车ID
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean deleteFromShoppingCart(Integer shoppingCartId) throws SqlErrorException {
        @Language("sql")
        String sql = "DELETE FROM shopping_cart WHERE shopping_cart_id = ?";
        Object[] params = {shoppingCartId};
        int result = executeUpdate(sql, params);
        return result == 1;
    }

    /**
     * 更新购物车中商品的数量
     *
     * @param shoppingCartId 购物车ID
     * @param quantity       商品数量
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateQuantity(Integer shoppingCartId, Integer quantity) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE shopping_cart SET quantity = ? WHERE shopping_cart_id = ?";
        Object[] params = {quantity, shoppingCartId};
        int result = executeUpdate(sql, params);
        return result == 1;
    }

    /**
     * 更新购物车中所有商品的选中状态
     *
     * @param userId 用户ID
     * @param status 选中状态，0表示未选中，1表示选中
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateAllSelectedStatus(Integer userId, Integer status) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE shopping_cart SET is_selected_status = ? WHERE information_id = ?";
        Object[] params = {status, userId};
        int result = executeUpdate(sql, params);
        return result > 0;
    }

    /**
     * 更新购物车商品的选中状态
     *
     * @param shoppingCartId 购物车ID
     * @param isSelected     商品选中状态，true表示选中，false表示未选中
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateSelectedStatus(Integer shoppingCartId, Integer isSelected) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE shopping_cart s left join commodity c on c.commodity_id = s.commodity_id SET is_selected_status = ? WHERE shopping_cart_id = ? and c.commodity_state != 2";
        Object[] params = {isSelected, shoppingCartId};
        int result = executeUpdate(sql, params);
        return result == 1;
    }

    /**
     * 根据用户信息表Id查询购物车信息，包括限购数量和库存数量，并查询出最大限购数量、赠品信息和关联的商品信息
     *
     * @param informationId 用户信息表Id
     * @return List<ShoppingCart>  集合对象
     * @throws SqlErrorException 抛出SQL错误
     */
    public List<ShoppingCart> getShoppingCartByInformationId(Integer informationId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT DISTINCT sc.shopping_cart_id, sc.is_selected_status,c.commodity_id, " +
                "c.commodity_id, c.commodity_name, c.commodity_price, " +
                "c.commodity_discount, " +
                "c.commodity_images, c.commodity_state, sc.quantity, " +
                "cw.details_inventory, cw.purchase_limit, sc.specification_ids,sc.insurance_ids, " +
                "cg.commodity_gifts_id, cg.gifts_number, cg.gifts_begin_date, cg.gifts_end_date, " +
                "cg.commodity_id AS gifts_commodity_id, gc.commodity_name AS gifts_commodity_name, " +
                "gc.commodity_price AS gifts_commodity_price, gc.commodity_images AS gifts_commodity_images, " +
                "gc.commodity_state AS gifts_commodity_state " +
                "FROM shopping_cart sc JOIN account_information ai " +
                "ON ai.information_id = sc.information_id JOIN commodity c " +
                "ON c.commodity_id = sc.commodity_id " +
                "JOIN (SELECT commodity_id, MAX(purchase_limit) AS max_purchase_limit " +
                "FROM commodity_warehouse GROUP BY commodity_id ) cw_max " +
                "ON c.commodity_id = cw_max.commodity_id JOIN ( " +
                "SELECT commodity_id, purchase_limit, details_inventory " +
                "FROM commodity_warehouse WHERE (commodity_id, purchase_limit) " +
                "IN (SELECT commodity_id, MAX(purchase_limit) FROM commodity_warehouse " +
                "GROUP BY commodity_id)) cw " +
                "ON cw.commodity_id = c.commodity_id " +
                "AND cw.purchase_limit = cw_max.max_purchase_limit " +
                "LEFT JOIN commodity_gifts cg ON cg.commodity_gifts_id = c.commodity_gifts " +
                "AND NOW() BETWEEN cg.gifts_begin_date AND cg.gifts_end_date " +
                "LEFT JOIN commodity gc ON gc.commodity_id = cg.commodity_id " +
                "WHERE ai.information_id = ? " +
                "ORDER BY c.commodity_state ASC ";
        ResultSet resultSet = executeQuery(sql, informationId);

        List<ShoppingCart> shoppingCarts = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ShoppingCart shoppingCart = getShoppingCartFromResultSet(resultSet);

                int commodityGiftsId = resultSet.getInt("commodity_gifts_id");
                if (commodityGiftsId != 0) {
                    CommodityGifts commodityGifts = new CommodityGifts();
                    commodityGifts.setCommodityGiftsId(commodityGiftsId);
                    commodityGifts.setGiftsNumber(resultSet.getInt("gifts_number"));
                    commodityGifts.setGiftsBeginDate(resultSet.getTimestamp("gifts_begin_date"));
                    commodityGifts.setGiftsEndDate(resultSet.getTimestamp("gifts_end_date"));

                    Commodity giftsCommodity = new Commodity();
                    giftsCommodity.setCommodityId(resultSet.getInt("gifts_commodity_id"));
                    giftsCommodity.setCommodityName(resultSet.getString("gifts_commodity_name"));
                    giftsCommodity.setCommodityPrice(resultSet.getBigDecimal("gifts_commodity_price"));
                    giftsCommodity.setCommodityImages(resultSet.getString("gifts_commodity_images").split(",")[0]);
                    giftsCommodity.setCommodityState(resultSet.getInt("gifts_commodity_state"));

                    commodityGifts.setCommodity(giftsCommodity);

                    shoppingCart.setCommodityGifts(commodityGifts);
                }

                shoppingCarts.add(shoppingCart);
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }

        return shoppingCarts;
    }


    /**
     * 修改购物车中商品的保险ID
     *
     * @param shoppingCartId 购物车ID
     * @param insuranceIds   保险ID
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateInsuranceIds(Integer shoppingCartId, String insuranceIds) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE shopping_cart SET insurance_ids = ? WHERE shopping_cart_id = ?";
        Object[] params = {insuranceIds, shoppingCartId};
        int result = executeUpdate(sql, params);
        return result == 1;
    }

    /**
     * 查询购物车中商品的数量总和
     *
     * @param informationId 用户信息表Id
     * @return 数量总和
     * @throws SqlErrorException SQL执行异常
     */
    public Integer getShoppingCartQuantity(Integer informationId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT SUM(quantity) FROM shopping_cart WHERE information_id = ?";
        ResultSet resultSet = executeQuery(sql, informationId);

        int quantity = 0;
        try {
            if (resultSet.next()) {
                quantity = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }

        return quantity;
    }

    /**
     * 查询购物车中是否存在已选中的商品
     *
     * @param informationId 用户信息表ID
     * @return 存在返回true，不存在返回false
     * @throws SqlErrorException SQL执行异常
     **/
    public boolean hasSelectedShoppingCart(Integer informationId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT COUNT(*) FROM shopping_cart WHERE information_id = ? AND is_selected_status = 1";
        ResultSet resultSet = executeQuery(sql, informationId);

        boolean hasSelected = false;
        try {
            if (resultSet.next()) {
                hasSelected = resultSet.getInt(1) > 0;
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }

        return hasSelected;
    }
    /**
     * 根据用户信息表Id查询被勾选购物车商品信息及对应的赠品商品信息
     * @param informationId 用户信息表Id
     * @return List<ShoppingCart>  集合对象
     * @throws SqlErrorException 抛出SQL错误
     */
    public List<ShoppingCart> getSelectedShoppingCartAndGiftsByInformationId(Integer informationId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT sc.shopping_cart_id, sc.is_selected_status, " +
                "c.commodity_id, c.commodity_name, c.commodity_price, c.commodity_images, c.commodity_state, " +
                "sc.quantity, sc.specification_ids, sc.insurance_ids, " +
                "cg.commodity_gifts_id, cg.gifts_number, cg.gifts_begin_date, cg.gifts_end_date, " +
                "c.commodity_gifts," +
                "c.commodity_discount, " +
                "gc.commodity_id     as gift_commodity_id, " +
                "gc.commodity_name   as gift_commodity_name, " +
                "gc.commodity_price  as gift_commodity_price, " +
                "gc.commodity_images as gift_commodity_images, " +
                "cc.category_id " +
                "FROM shopping_cart sc " +
                "JOIN account_information ai ON ai.information_id = sc.information_id " +
                "JOIN commodity c ON c.commodity_id = sc.commodity_id " +
                "LEFT JOIN commodity_gifts cg ON cg.commodity_gifts_id = c.commodity_gifts " +
                "AND NOW() BETWEEN cg.gifts_begin_date AND cg.gifts_end_date " +
                "LEFT JOIN commodity gc ON gc.commodity_id = cg.commodity_id " +
                "LEFT JOIN class cl ON cl.class_id = c.commodity_class " +
                "LEFT JOIN category cc ON cc.category_id = cl.category_id " +
                "WHERE ai.information_id = ? AND sc.is_selected_status = 1 " +
                "ORDER BY c.commodity_state ASC ";
        ResultSet resultSet = executeQuery(sql, informationId);

        List<ShoppingCart> shoppingCarts = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ShoppingCart shoppingCart = new ShoppingCart();
                shoppingCart.setShoppingCartId(resultSet.getInt("shopping_cart_id"));
                shoppingCart.setIsSelectedStatus(resultSet.getInt("is_selected_status"));
                shoppingCart.setQuantity(resultSet.getInt("quantity"));
                shoppingCart.setSpecificationIds(resultSet.getString("specification_ids"));
                shoppingCart.setInsuranceIds(resultSet.getString("insurance_ids"));

                Commodity commodity = new Commodity();
                commodity.setCommodityId(resultSet.getInt("commodity_id"));
                commodity.setCommodityName(resultSet.getString("commodity_name"));
                commodity.setCommodityPrice(resultSet.getBigDecimal("commodity_price"));
                commodity.setCommodityDiscount(resultSet.getInt("commodity_discount"));
                commodity.setCommodityImages(resultSet.getString("commodity_images").split(",")[0]);
                commodity.setCommodityState(resultSet.getInt("commodity_state"));
                //商品种类
                commodity.setCategory(new Category(resultSet.getInt("category_id")));
                shoppingCart.setCommodity(commodity);

                int commodityGiftsId = resultSet.getInt("commodity_gifts_id");
                if (commodityGiftsId != 0) {
                    CommodityGifts commodityGifts = new CommodityGifts();
                    commodityGifts.setCommodityGiftsId(commodityGiftsId);
                    commodityGifts.setGiftsNumber(resultSet.getInt("gifts_number"));
                    commodityGifts.setGiftsBeginDate(resultSet.getTimestamp("gifts_begin_date"));
                    commodityGifts.setGiftsEndDate(resultSet.getTimestamp("gifts_end_date"));

                    Commodity giftsCommodity = new Commodity();
                    giftsCommodity.setCommodityId(resultSet.getInt("gift_commodity_id"));
                    giftsCommodity.setCommodityName(resultSet.getString("gift_commodity_name"));
                    giftsCommodity.setCommodityPrice(resultSet.getBigDecimal("gift_commodity_price"));
                    giftsCommodity.setCommodityImages(resultSet.getString("gift_commodity_images").split(",")[0]);
                    commodityGifts.setCommodity(giftsCommodity);

                    shoppingCart.setCommodityGifts(commodityGifts);
                }

                shoppingCarts.add(shoppingCart);
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }

        return shoppingCarts;
    }
    /**
     * 根据用户信息表ID和选中状态查询购物车商品信息
     *
     * @param informationId 用户信息表ID
     * @return List<ShoppingCart>  集合对象
     * @throws SqlErrorException 抛出SQL错误
     */
    public List<ShoppingCart> getSelectedShoppingCartByInformationIdTwo(Integer informationId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT shopping_cart_id, insurance_ids, specification_ids, commodity_id FROM shopping_cart WHERE information_id = ? AND is_selected_status = 1";
        ResultSet resultSet = executeQuery(sql, informationId);

        List<ShoppingCart> shoppingCarts = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ShoppingCart shoppingCart = new ShoppingCart();
                shoppingCart.setShoppingCartId(resultSet.getInt("shopping_cart_id"));
                shoppingCart.setInsuranceIds(resultSet.getString("insurance_ids"));
                shoppingCart.setSpecificationIds(resultSet.getString("specification_ids"));

                Commodity commodity = new Commodity();
                commodity.setCommodityId(resultSet.getInt("commodity_id"));
                shoppingCart.setCommodity(commodity);

                shoppingCarts.add(shoppingCart);
            }
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }

        return shoppingCarts;
    }

    /**
     * 查询结果集
     * @param resultSet 结果集
     * @return ShoppingCart对象
     * @throws SQLException 抛出错误
     */
    private ShoppingCart getShoppingCartFromResultSet(ResultSet resultSet) throws SQLException {
        ShoppingCart shoppingCart = new ShoppingCart();
        shoppingCart.setShoppingCartId(resultSet.getInt("shopping_cart_id"));
        shoppingCart.setIsSelectedStatus(resultSet.getInt("is_selected_status"));
        shoppingCart.setQuantity(resultSet.getInt("quantity"));
        shoppingCart.setSpecificationIds(resultSet.getString("specification_ids"));
        shoppingCart.setInsuranceIds(resultSet.getString("insurance_ids"));

        Commodity commodity = new Commodity();
        commodity.setCommodityId(resultSet.getInt("commodity_id"));
        commodity.setCommodityName(resultSet.getString("commodity_name"));
        commodity.setCommodityDiscount(resultSet.getInt("commodity_discount"));
        commodity.setCommodityPrice(resultSet.getBigDecimal("commodity_price"));
        commodity.setCommodityImages(resultSet.getString("commodity_images").split(",")[0]);
        commodity.setCommodityState(resultSet.getInt("commodity_state"));

        commodity.setCommodityWarehouse(new CommodityWarehouse(
                resultSet.getInt("details_inventory"),
                resultSet.getInt("purchase_limit")
        ));

        shoppingCart.setCommodity(commodity);

        return shoppingCart;
    }
    /**
     * 根据购物车ID删除购物车中商品
     *
     * @param shoppingCartId 购物车ID
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean deleteShoppingCartById(Integer shoppingCartId) throws SqlErrorException {
        @Language("sql")
        String sql = "DELETE FROM shopping_cart WHERE shopping_cart_id = ?";
        Object[] params = {shoppingCartId};
        int result = executeUpdate(sql, params);
        return result == 1;
    }
    /**
     * 根据commodityId和informationId是否有商品有则修改数量没有则添加
     * @param shoppingCart 购物车实体类
     * @return boolean true修改成功 false修改失败
     */
    public boolean getShoppingCartByCommodityIdAndInformationId(ShoppingCart shoppingCart) {
        @Language("SQL")
        String selectSql = "SELECT * FROM shopping_cart WHERE commodity_id = ? AND information_id = ?";
        @Language("SQL")
        String updateSql = "UPDATE shopping_cart SET quantity = ? WHERE commodity_id = ? AND information_id = ?";
        @Language("SQL")
        String insertSql = "INSERT INTO shopping_cart " +
                "(commodity_id, information_id, quantity,is_selected_status,specification_ids,insurance_ids) " +
                "VALUES (?, ?, ? ,? ,? ,?)";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(selectSql, shoppingCart.getCommodityId(), shoppingCart.getInformationId());

            if (resultSet.next()) {
                // 商品已存在，执行更新数量操作
                int currentQuantity = resultSet.getInt("quantity");
                int newQuantity = currentQuantity + 1;

                return executeUpdate(updateSql, newQuantity, shoppingCart.getCommodityId(), shoppingCart.getInformationId()) != 0;
            } else {
                // 商品不存在，执行添加操作
                return executeUpdate(insertSql, shoppingCart.getCommodityId(),
                        shoppingCart.getInformationId(),
                        1,1,shoppingCart.getSpecificationIds(),shoppingCart.getInsuranceIds()) != 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(resultSet);
        }

        return false;
    }
}
